package instructor.sample;
import java.sql.*;

public class Equipment {
	
	private ResultSet rs = null;
	private PreparedStatement ps = null;
	private OraDB myDB = new OraDB();
	private int myequipmentid = 0;

	
	public ResultSet getEquipmentInfo(int equipmentid)
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myequipmentid = equipmentid;
		myDB.createConnection();
		String myQuery = "select p.modalityid, m.modalitydescription, e.equipmentid, " +
		  "p.productname, s.statusid, s.StatusDescription, " +
		  "to_char(e.inservicedate, 'MM/DD/YYYY') as inservicedate, " +
		  "l.address1 || ', ' || l.address2 || ' ' || l.city || ', ' || l.state || '  '|| l.zip as location, " +
		  "sc.servicecontractdescription, p.oem " +
		  "from status s, customer c, location l, product p, modality m, equipment e, servicecontract sc " +
		  "where e.StatusID = s.StatusID and " +
		  "e.locationid = l.locationid and " + 
		  "l.customerid = c.customerid and " +
		  "e.productid = p.productid and " +
		  "p.modalityid = m.modalityid and " +
		  "c.servicecontractid = sc.servicecontractid and " +
		  "e.equipmentid = ? " +
		  "order by 1,5,3";
		
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1,myequipmentid); 
		rs = ps.executeQuery();
		return rs;
	}
	
	public ResultSet getHistory(int equipmentid)
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myequipmentid = equipmentid;
		myDB.createConnection();
		String myQuery = "select so.serviceorderid, st.servicetypedescription, " +
				"to_char(so.opendate, 'MM/DD/YYYY') as opendate, " + 
				"pri.prioritydescription, sos.soStatusDescription, so.problemdescription " +
				"from serviceorder so, servicetype st, ServiceOrderStatus sos, priority pri, equipment e " +
				"where e.equipmentid=so.equipmentid and " +
				"so.priorityid=pri.priorityid and " +
				"so.servicetypeid=st.servicetypeid and " +
				"so.soStatusID = sos.soStatusID and " +
				"so.equipmentid = ? " +
				"order by 1";
		
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1,myequipmentid); 
		rs = ps.executeQuery();
		return rs;
	}
	
	
	public void closeConnection()
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		 myDB.closeConnection();
	}

}
